package com.apt.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.apt.common.AptException;
import com.apt.common.DBUtility;
import com.apt.domain.StudentResult;

public class StudentReportDao extends BaseConnection {

	public StudentReportDao(Connection conn) {
		super(conn);

	}

	public List<String> getStudentsForCollege(int collegeId)
			throws AptException {
		List<String> stu = new ArrayList<String>();

		try {

			PreparedStatement ps = connection
					.prepareStatement("SELECT USER_ID from USER_MASTER WHERE INSTITUTION_ID = ? ");
			ps.setInt(1, collegeId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {

				stu.add(rs.getString(1));

			}
			rs.close();

		} catch (SQLException sqlEx) {
			sqlEx.printStackTrace();
			throw new AptException(
					"Unable to get Students for college with collegeId:"
							+ collegeId);

		} finally {
			DBUtility.cleanUp(connection);

		}
		return stu;

	}

	public List<StudentResult> getResults(int collegeId) throws AptException {

		List<StudentResult> stu = new ArrayList<StudentResult>();
		

		try {
			
			PreparedStatement ps = connection
					.prepareStatement("SELECT A.INSTITUTION_ID,A.USER_ID,A.FIRST_NAME,B.TEST_ID,A.DEPARTMENT,B.QUESTIONS_CORRECT,B.PERCENTAGE " +
							",B.USER_TEST_EVAL_ID from USER_MASTER A, USER_TEST_EVAL B " +
							"WHERE A.INSTITUTION_ID = ? AND A.USER_ID = B.USER_ID ORDER BY B.USER_TEST_EVAL_ID");
			ps.setInt(1, collegeId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {

				stu.add(new StudentResult(rs.getInt("TEST_ID"),rs.getString("USER_ID"),rs.getString("FIRST_NAME"),rs.getString("DEPARTMENT"),rs.getInt("QUESTIONS_CORRECT"),rs.getDouble("PERCENTAGE")));

			}
			rs.close();

		} catch (SQLException sqlEx) {
			sqlEx.printStackTrace();
			throw new AptException(
					"Unable to get Student Results for college with collegeId:"
							+ collegeId);

		} finally {
			DBUtility.cleanUp(connection);

		}
		return stu;

	}

}
